package henu.dao.impl;

import henu.bean.LiPinfomation;
import henu.bean.License;
import henu.dao.LicenseDao;
import henu.dao.factory.DaoFactory;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;



public class LicenseDaoImpl implements LicenseDao {

	@Override
	public int save(License li) {
		QueryRunner runner = DaoFactory.getRunner();
		int result = 0;
		String sql = "INSERT INTO LICENSE VALUES(?,?,?,?,?,?,?,?)";
		Object[] params = {li.getIdcard(),li.getCartype(),li.getFirsttime(),li.getLimittime(),
				li.getBegintime(),li.getYearaudit(),li.getLphoto(),li.getMemo()};
		try {
			result = runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delete(String leid) {
		String sql = "DELETE FROM LICENSE where idcard=?";
		int result = 0;
		QueryRunner runner = DaoFactory.getRunner();
		try {
			result = runner.update(sql,leid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int update(String leid, License li) {
		String sql = "UPDATE LICENSE SET idcard=?,cartype=?,firsttime=?,limittime=?,begintime=?,yearaudit=?,lphoto=?,memo=? WHERE idcard=?";
		int result = 0;
		Object[] params = {li.getIdcard(),li.getCartype(),li.getFirsttime(),li.getLimittime(),li.getBegintime(),li.getYearaudit(),
							li.getLphoto(),li.getMemo(),leid};
		QueryRunner runner = DaoFactory.getRunner();
		try {
			result = runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public List<License> findAll(String order, String sort) {
		List <License> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "select * from LICENSE  order by " + order + " " + sort + "";
		try {
			list = runner.query(sql, new BeanListHandler<License>(License.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<LiPinfomation> findByProperty(String property, String key,String order, String sort, int start, int end,int comid) {
			List<LiPinfomation> list= null;
			QueryRunner runner = DaoFactory.getRunner();
		/*确立一个comid用视图查找*/	String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from VIEW_LI_PINFO where "+property+"='"+key+"' and comid="+comid+"  order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			try {
				list = runner.query(sql, new BeanListHandler<LiPinfomation>(LiPinfomation.class));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;
	}
	
	@Override
	public List<License> findByDetial(String leid) {
		List <License> list = null;
		String sql = "select * from LICENSE where idcard=?";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			list = runner.query(sql, new BeanListHandler<License>(License.class),leid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public License findById(String leid) {
		/*System.out.println("id2+\t"+leid);*/
		License li = null;
		String sql = "select * from LICENSE where idcard=?";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			li = runner.query(sql,new BeanHandler<License>(License.class), leid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return li;
	}

}
